IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFundIDsAndTotals]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetFundIDsAndTotals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
Generate a resultset containing two tables; one, the list of funds accessible to the specified user after applying the specified
filter criteria, and two, the total of monies.
Each fund is identified by its ID.
In addition to the fund ID, the resultset will also contain the access level associated with each fund.
*/

--------------------------------------------------------------------
--Modifications:
--09-04-2007	Performance improvements: introduce (nolock)
----------------
--11-16-2007	Implemented "N/A" in place of Blank PI First and Last names. Trackit # 7319.
--------------------------------------------------------------------
CREATE procedure [dbo].[GetFundIDsAndTotals]    
(    
 @SessionId varchar(100),    
 @domainUserId int,    
 @lastGLPostDate datetime = null,    
 @glPostAsOfDate datetime = null,    
 @includeIndirect bit = 1,    
 @fundNumber varchar(8) = '',    
 @folderNumber varchar(16) = '',    
 @bicsNumber varchar(10) = '',    
 @walkerNumber varchar(10) = '',    
 @sponsorNumber varchar(32) = '',    
 @sponsorName varchar(64) = '',    
 @sponsorTypeId int = 0,    
 @agreementTypeId int = 0,    
 @activityCode varchar(4) = '',    
 @locationId int = 0,    
 @budgetStartDate datetime = '1/1/1900',    
 @budgetEndDate datetime = '1/1/1900',
 @budgetEndFrom datetime = '1/1/1900', 
 @budgetEndTo datetime = '1/1/1900', 
 @projectStartDate datetime = '1/1/1900',    
 @projectEndDate datetime = '1/1/1900', 
 @projectEndFrom datetime = '1/1/1900',
 @projectEndTo datetime = '1/1/1900',
 @projectTitle varchar(200) = '',    
 @piName varchar(195) = '',    
 @chiefCodeList varchar(1000) = '',    
 @sponsorGroupTypeId integer = 0,    
 @includeProgramProjects bit = 0,    
 @daysBudgetEnding integer = 0,    
 @daysProjectEnding integer = 0,    
 @daysFundCreated integer = 0,    
 @lowBalance money = -922337203685477,    
 @highBalance money = 922337203685477,    
 @agreementStatusTypeList varchar(50) = '',    
 @fundStatusTypeId int = 0,    
 @sortColumn varchar(100) = null ,  
 @viewTypeID int = null,
 @documentId varchar(100) = null  
)    
as    
begin    
 set nocount on    
    
 declare @today datetime    
 set @today = GetDate()    
    
 declare @viewFinancialInfoBitValue int    
 set @viewFinancialInfoBitValue = 4    
    
 declare @viewPendingAgreementsBitValue int    
 set @viewPendingAgreementsBitValue = 32    
    
 declare @authorizedRightsBitMask int    
 set @authorizedRightsBitMask = @viewFinancialInfoBitValue + @viewPendingAgreementsBitValue    
    
 set @budgetEndDate = isnull(@budgetEndDate, @today)    
 set @projectEndDate = isnull(@projectEndDate, @today)    
    set @projectTitle = isnull(@projectTitle, '')    
 set @lastGLPostDate = isnull(@lastGLPostDate, @today)    
 set @glPostAsOfDate = isnull(@glPostAsOfDate, @today)    
    
 --Compute the last month GL Post start and end dates and their date keys. The last GL post date is always at the end of a month.    
    
 declare @lastMonthStart datetime    
 set @lastMonthStart = cast(datepart(mm,@glPostAsOfDate) as varchar) + '/1/' + cast(datepart(yyyy,@glPostAsOfDate) as varchar)    
    
 -- Generate a table of Funds (identified by the respective ID) that the user has access to, based on the specified criteria.    
 -- This will be used to limit the final resultset.    
    
 IF OBJECT_ID('tempdb..#TempFundCollection') is not null drop table #TempFundCollection    
 create table #TempFundCollection (FundID int not null, AccessLevel int not null)    
    
 exec FilterFundsToTable @SessionId, @domainUserId, @authorizedRightsBitMask, @includeProgramProjects, @fundNumber,    
      @folderNumber, @bicsNumber, @walkerNumber, @sponsorNumber, @sponsorName, @sponsorTypeId,    
      @agreementTypeId, @activityCode, @locationId, @budgetStartDate, @budgetEndDate,@budgetEndFrom,@budgetEndTo,    
      @projectStartDate, @projectEndDate,@projectEndFrom,@projectEndTo,@projectTitle, @piName, @chiefCodeList, @sponsorGroupTypeId,    
      @daysBudgetEnding, @daysProjectEnding, @daysFundCreated, @lowBalance, @highBalance,    
      @agreementStatusTypeList, @fundStatusTypeId, @viewTypeID, @documentId 
    
 -- Generate a table of fund information such that we may apply the specified sort order to that table and return that as the    
 -- final resultset.    
    
 select    
  aaf.FundID,    
  AgreementId,    
  AgrAgreementType as AgreementType,    
  FundNumber,    
  case    
   when @includeIndirect = 1 then isnull(DirectBudget,0) + isnull(IndirectBudget,0)    
   else isnull(DirectBudget,0)    
  end as TotalBudgetAmount,    
  case    
   when @includeIndirect = 1 then isnull(LastMonthDirectExpenses,0) + isnull(LastMonthIndirectExpenses,0)    
   else isnull(LastMonthDirectExpenses,0)    
  end as LastMoExpenses,    
  case    
   when @includeIndirect = 1 then isnull(DirectExpenses,0) + isnull(IndirectExpenses,0)    
   else isnull(DirectExpenses,0)    
  end as ExpensesToDate,    
  case    
   when @includeIndirect = 1 then (isnull(DirectBudget,0) + isnull(IndirectBudget,0) ) - (isnull(DirectExpenses,0) + isnull(IndirectExpenses,0))    
   else isnull(DirectBudget,0) - isnull(DirectExpenses,0)    
  end as BudgetBalance,    
  -isnull(Revenue,0) as Revenue,    
  case    
   when @includeIndirect = 1 then -(isnull(BeginningBalance,0) + isnull(Revenue,0) + isnull(DirectExpenses,0)+isnull(IndirectExpenses,0))    
   else -(isnull(BeginningBalance,0) + isnull(Revenue,0) + isnull(DirectExpenses,0))    
  end as CurrentCashBalance,    
  case    
   when @includeIndirect = 1 then isnull(DirectPOAPEncumbrances,0) + isnull(IndirectPOAPEncumbrances,0)    
   else isnull(DirectPOAPEncumbrances,0)    
  end as OpenPOAPBalance,    
  case    
   when @includeIndirect = 1 then isnull(DirectSalaryEncumbrances,0)+isnull(IndirectSalaryEncumbrances,0)    
   else isnull(DirectSalaryEncumbrances,0)    
  end as PlannedSalary,    
  case    
   --when AgrAgreementType = 'grant' or AgrAgreementType = 'subcontract'    
   -- or (AgrAgreementType = 'trial' and isnull(SponsorGroupTypeId,0) <= 2)    
   when IsCashBased = 0     
   then    
    case    
     when @includeIndirect = 1 then     
      (isnull(DirectBudget,0) + isnull(IndirectBudget,0)) -     
      isnull(DirectExpenses,0) - isnull(IndirectExpenses,0) -    
      isnull(DirectPOAPEncumbrances,0) - isnull(IndirectPOAPEncumbrances,0) -     
      isnull(DirectSalaryEncumbrances,0) - isnull(IndirectSalaryEncumbrances,0)    
     else    
      isnull(DirectBudget,0) - isnull(DirectExpenses,0) -    
      isnull(DirectPOAPEncumbrances,0) - isnull(DirectSalaryEncumbrances,0)    
    end    
   --when AgrAgreementType = 'gift' or AgrAgreementType = 'exchange' or AgrAgreementType = 'misc/other'    
   -- or (AgrAgreementType = 'trial' and isnull(SponsorGroupTypeId,0) > 2)    
   --then    
   Else    
    case    
     when @includeIndirect = 1 then     
      -(isnull(BeginningBalance,0) + isnull(Revenue,0) + isnull(DirectExpenses,0) + isnull(IndirectExpenses,0) +    
      isnull(DirectPOAPEncumbrances,0) + isnull(IndirectPOAPEncumbrances,0) +     
      isnull(DirectSalaryEncumbrances,0) + isnull(IndirectSalaryEncumbrances,0))    
     else    
      -(isnull(BeginningBalance,0) + isnull(Revenue,0) + isnull(DirectExpenses,0) +    
      isnull(DirectPOAPEncumbrances,0) + isnull(DirectSalaryEncumbrances,0))    
    end    
  end as ProjectedBalance,    
  ProjectStartDate,    
  ProjectEndDate,    
  BudgetStartDate,    
  BudgetEndDate,    
  isnull(ff.AccessLevel,0) as AccessLevel,    
  case isnull(aaf.PILastName,'') + isnull(aaf.PIFirstName,'') when '' then 'N/A'    
  else    
  isnull(aaf.PILastName,'') + ', ' + isnull(aaf.PIFirstName,'')    
  end     
  as PILastFirstName    
 into #results    
 from dbo.AgrAgreement_Fct aaf (nolock)    
 inner join #TempFundCollection ff 
 on ff.FundId = aaf.FundId
    
 -- Order the resultset by the specified sort column; if none is specified, then by CurrentCashBalance.    
 if ((@sortColumn is null) or (@sortColumn = 'CurrentCashBalance'))    
  select FundID, AccessLevel from #results order by CurrentCashBalance    
 else    
 begin    
  exec('select FundID, AccessLevel from #results order by ' + @sortColumn)    
 end    
    
 select    
  sum(TotalBudgetAmount) as TotalBudgetAmount,    
  sum(LastMoExpenses) as TotalLastMoExpenses,    
  sum(ExpensesToDate) as TotalExpensesToDate,    
  sum(BudgetBalance) as TotalBudgetBalance,    
  sum(Revenue) as TotalRevenue,    
  sum(CurrentCashBalance) as TotalCurrentCashBalance,    
  sum(OpenPOAPBalance) as TotalOpenPOAPBalance,    
  sum(PlannedSalary) as TotalPlannedSalary,    
  sum(ProjectedBalance) as TotalProjectedBalance    
 from #results    
    
 IF OBJECT_ID(N'tempdb..#results', N'U') IS NOT NULL  drop table #results    
 IF OBJECT_ID(N'tempdb..#TempFundCollection', N'U') IS NOT NULL  drop table #TempFundCollection    
end    
GO